﻿using Devart.Data.SQLite;
using OR;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace JMI_Nfe.UI
{
    public partial class FrmConIBGESQLite : Form
    {
        const string tabela = "ibge";

        private string arquivoSQLite = String.Empty;
        private string DiretorioArquivo = String.Empty;
        private SQLiteConnection conn = null;
        private SQLiteDataAdapter adapter = null;
        private SQLiteCommand comando = null;
        private int pagina = 0;
        private DataTable tb = new DataTable();
        private DataSet dataset = null;
        private DataRow rwRegistro = null;
        private string aux = String.Empty;

        private int numeroIBGE;

        public int NumeroIBGE { get { return numeroIBGE; } set { numeroIBGE = value; } }

        public FrmConIBGESQLite()
        {
            InitializeComponent();
            DiretorioArquivo = ConfigurationManager.AppSettings.Get("PathArquivoNFe");
            arquivoSQLite = Path.Combine(DiretorioArquivo, "ibge.db");
        }

        private void BtnCancelar_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void CarregarLista()
        {
           bsIBGE.SuspendBinding();
           try {
                bsIBGE.DataSource = Listar(this.pagina);
                //bsIBGE.DataMember = dataset.Tables[0].TableName;
                Application.DoEvents();
                if (bsIBGE.List.Count == 0)
                {
                  BtnProximo.Enabled = false;
                  this.pagina--;
                  this.CarregarLista();
                } 
                else
                    BtnProximo.Enabled = (bsIBGE.List.Count >= 1); //(bsIBGE.List.Count == Linhas);
               
                BtnAnterior.Enabled = (this.pagina > 0);
                Application.DoEvents();
           } 
           finally
           {
               bsIBGE.ResumeBinding();
               dgvRegistro.Select();
           }
        }

        public List<mIBGE> Listar(int pagina)
        {
            conn = new SQLiteConnection();
            conn.DataSource = arquivoSQLite;
            conn.Open();

            adapter = new SQLiteDataAdapter();
            comando = new SQLiteCommand();
            dataset = new DataSet();

            try
            {
                dataset.Tables.Add(new DataTable(tabela));

                comando.CommandTimeout = 0;
                comando.Connection = conn;
                comando.CommandText = @"SELECT * FROM ibge ORDER BY municipio";

                adapter.SelectCommand = comando;

                if (pagina > -1)
                    adapter.Fill(dataset, pagina * 25, 25, tabela);
                else
                    adapter.Fill(dataset, tabela);

                List<mIBGE> cache = new List<mIBGE>();
                mIBGE _mIBGE;

                foreach (DataRow row in dataset.Tables[tabela].Rows)
                {
                    // Popula a classe com os dados do DataRow
                    _mIBGE = new mIBGE();
                    _mIBGE.Codigo = Convert.ToInt32(row["ID"]);
                    _mIBGE.Municipio = row["MUNICIPIO"].ToString();
                    _mIBGE.Numero = Convert.ToInt32(row["NUMERO"]);
                    _mIBGE.UF = row["DESCRICAOUF"].ToString();
                    _mIBGE.CodUF = Convert.ToInt16(row["UF"]);
                    cache.Add(_mIBGE);
                }
                return cache;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed) { conn.Close(); }
                dataset.Dispose();
                adapter.Dispose();
                comando.Dispose();
                conn.Dispose();
            }
        }

        public mIBGE GetRegistro(string Campo, string Filtro)
        {
            conn = new SQLiteConnection();
            conn.DataSource = arquivoSQLite;
            conn.Open();

            adapter = new SQLiteDataAdapter();
            comando = new SQLiteCommand();
            dataset = new DataSet();

            try
            {
                dataset.Tables.Add(new DataTable(tabela));

                comando.CommandTimeout = 0;
                comando.Connection = conn;
                comando.CommandText = @"SELECT * FROM ibge WHERE " + Campo + Filtro;

                adapter.SelectCommand = comando;

                adapter.Fill(dataset, tabela);

                mIBGE _mIBGE = new mIBGE();

                if (dataset.Tables[tabela].Rows.Count > 0)
                {
                    rwRegistro = dataset.Tables[tabela].Rows[0];
                    _mIBGE = new mIBGE();
                    _mIBGE.Codigo = Convert.ToInt32(rwRegistro["ID"]);
                    _mIBGE.Municipio = rwRegistro["MUNICIPIO"].ToString();
                    _mIBGE.Numero = Convert.ToInt32(rwRegistro["NUMERO"]);
                    _mIBGE.UF = rwRegistro["DESCRICAOUF"].ToString();
                    _mIBGE.CodUF = Convert.ToInt16(rwRegistro["UF"]);
                }
                return _mIBGE;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed) { conn.Close(); }
                dataset.Dispose();
                adapter.Dispose();
                comando.Dispose();
                conn.Dispose();
            }
        }

        private void BtnProximo_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.AppStarting;
            try
            {
                this.pagina++;
                CarregarLista();
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }

        private void BtnAnterior_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.AppStarting;
            try
            {
                if (pagina > 0) this.pagina--;
                CarregarLista();
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }

        private void FrmConIBGESQLite_Load(object sender, EventArgs e)
        {
            CarregarLista();
            TxtPesquisa.Focus();
        }
      
        private void TxtPesquisa_TextChanged(object sender, EventArgs e)
        {
            if (rbNumero.Checked && TxtPesquisa.Text.Length == 7)
            {
                tb = dataset.Tables[0];
                for (int x = 0; x < tb.Rows.Count; x++)
                {
                    rwRegistro = tb.Rows[x];
                    aux = rwRegistro["NUMERO"].ToString();
                    if (TxtPesquisa.Text == aux)
                    {
                        bsIBGE.DataSource = GetRegistro("NUMERO", " = '" + TxtPesquisa.Text + "'");
                        break;
                    }
                }
            }
            else if (rbMunicipio.Checked && TxtPesquisa.Text.Length > 2)
            {
                tb = dataset.Tables[0];
                for (int x = 0; x < tb.Rows.Count; x++)
                {
                    rwRegistro = tb.Rows[x];
                    aux = rwRegistro["MUNICIPIO"].ToString();
                    if (TxtPesquisa.Text.ToUpper() == aux.ToUpper())
                    {
                        bsIBGE.DataSource = GetRegistro("MUNICIPIO", " LIKE '" + TxtPesquisa.Text + "%'");
                        break;
                    }
                }
            }
            else if (TxtPesquisa.Text == String.Empty)
                bsIBGE.DataSource = tb;
            dgvRegistro.Refresh();
        }

        private void rbNumero_Click(object sender, EventArgs e)
        {
            TxtPesquisa.Focus();
        }

        private void rbMunicipio_Click(object sender, EventArgs e)
        {
            TxtPesquisa.Focus();
        }

        private void BtnOk_Click(object sender, EventArgs e)
        {
            if (dgvRegistro.SelectedRows.Count > 0)
            {
                int selecionado = dgvRegistro.CurrentRow.Index;
                string s = dgvRegistro.Rows[selecionado].Cells["colNumero"].Value.ToString();
                if (!String.IsNullOrEmpty(s))
                    NumeroIBGE = Convert.ToInt32(s);
                Close();
            }
        }

        private void FrmConIBGESQLite_KeyUp(object sender, KeyEventArgs e)
        {
            switch (e.KeyCode)
            {
                case Keys.F3:
                    BtnOk_Click(sender, new EventArgs());
                    break;
                case Keys.Escape:
                    BtnCancelar_Click(sender, new EventArgs());
                    break;
            }
        } 
    }    
}
